package team.yidong.fenda.serviceImpl;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import team.yidong.fenda.entity.*;
import team.yidong.fenda.repository.HospitalRepository;
import team.yidong.fenda.repository.MasterRepository;
import team.yidong.fenda.repository.SysUserRepository;
import team.yidong.fenda.service.MasterService;
import team.yidong.fenda.utils.ExcelImportUtils;
import team.yidong.fenda.utils.QiNiuYunUtil;

import javax.persistence.criteria.*;
import java.io.*;
import java.util.*;

/**
 * Created by xuanlong on 2017/10/24.
 */
@Service
public class MasterServiceImpl implements MasterService {
    @Autowired
    MasterRepository masterRepository;
    @Autowired
    HospitalRepository hospitalRepository;
    @Autowired
    QiNiuYunUtil qiNiuYunUtil;
    @Autowired
    SysUserRepository sysUserRepository;

    @Transactional
    @Override
    public String addMaster(String name, String info, String goodAt, String hospitalName, String userName, String password, Integer departmentId, String phoneNum) {

        Master master = new Master();
        if (sysUserRepository.findByUserName(userName) == null) {
            master.setUserName(userName);
        } else {
            return "用户名已存在,请重新输入信息";
        }
        master.setPassword(password);
        master.setName(name);
        master.setInfo(info);
        master.setHospitalDepartmentId(departmentId);
        master.setPhoneNumber(phoneNum);
        master.setIsBlack("否");
        master.setGoodAt(goodAt);
        Hospital hospital = hospitalRepository.findByName(hospitalName);
        master.setHospital(hospital);
        master.setHeadImg("https://qny.liuxuanlong.xyz/timg%20%283%29.jpg");
        masterRepository.save(master);
        SysUser sysUser = new SysUser();
        sysUser.setSysUserRoleId(3);
        sysUser.setUserName(userName);
        sysUser.setPassword(password);
        sysUser.setHospitalId(hospitalRepository.findByName(hospitalName).getId());
        sysUserRepository.save(sysUser);
        return "添加成功";

    }

    @Override
    public JSONObject findMaster(Integer hospitalId, String hospitalName, String name, String goodAt, Double scoreNum, String masterInfo, Integer offset, Integer limit) {
        Pageable pageable = new PageRequest(offset, limit, Sort.Direction.ASC, "id");
        Page<Master> masterPage = masterRepository.findAll(new Specification<Master>() {
            @Override
            public Predicate toPredicate(Root<Master> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicateList = new ArrayList<>();

                if (hospitalId != 1) {
                    Join<Master, Hospital> join = root.join("hospital");
                    Predicate p = criteriaBuilder.equal(join.get("id").as(Integer.class), hospitalId);
                    predicateList.add(p);
                }
                if (hospitalName != null && !hospitalName.equals("")) {
                    Hospital hospital = hospitalRepository.findByNameLike("%" + hospitalName + "%");
                    Predicate p = criteriaBuilder.equal(root.get("hospital").as(Hospital.class), hospital);
                    predicateList.add(p);
                }
                if (name != null && !name.equals("")) {
                    Predicate p = criteriaBuilder.like(root.get("name").as(String.class), "%" + name + "%");
                    predicateList.add(p);
                }
                if (goodAt != null && !goodAt.equals("")) {
                    Predicate p = criteriaBuilder.like(root.get("goodAt").as(String.class), "%" + goodAt + "%");
                    predicateList.add(p);
                }
                if (masterInfo != null && !masterInfo.equals("")) {
                    Predicate p = criteriaBuilder.like(root.get("info").as(String.class), "%" + masterInfo + "%");
                    predicateList.add(p);
                }
            /*    if (scoreNum != null && !scoreNum.equals("")) {
                    Join<Master, Answer> join = root.join("masterAnswerList");
                    Join<Join, MasterScore> join1 = join.join("masterScore");
                    Predicate predicate = criteriaBuilder.equal(criteriaBuilder.sum(join1.get("num")), scoreNum);
                    predicateList1.add(predicate);
                }
                criteriaQuery.having((predicateList1.toArray(new Predicate[predicateList1.size()])));*/
                criteriaQuery.where(predicateList.toArray(new Predicate[predicateList.size()]));
                return null;
            }
        }, pageable);

        JsonConfig config = new JsonConfig();
        config.setExcludes(new String[]{"hospitalPermissionSet", "master", "hospitalImgSet", "hospitalFieldList", "userSet", "masterList", "answer", "praiseUserSet", "answerList", "question", "masterScoreAnswer", "answerImgSet"});
        JSONObject jsonObject = JSONObject.fromObject(masterPage, config);
        String temp = "{\"total\":" + jsonObject.getString("totalElements") + ",\"rows\":" + jsonObject.getString("content") + "}";
        JSONObject j = JSONObject.fromObject(temp);


        return j;

    }

    @Transactional
    @Override
    public void updateMaster(Master master) {
        masterRepository.save(master);
    }

    @Override
    public void updateMasterInfo(Integer masterId, String masterName, String masterGoodAt, String masterInfo, String masterUserName, String masterpassword) {
        Master master = masterRepository.findOne(masterId);
        master.setName(masterName);
        master.setGoodAt(masterGoodAt);
        master.setInfo(masterInfo);
        master.setUserName(masterUserName);
        master.setPassword(masterpassword);
        masterRepository.save(master);
        SysUser sysUser = sysUserRepository.findByUserName(masterUserName);
        sysUser.setUserName(masterUserName);
        sysUser.setPassword(masterpassword);
        sysUserRepository.save(sysUser);
    }

    @Transactional
    @Override
    public void deleteMaster(Iterable<Master> masterIterable) {
        masterRepository.delete(masterIterable);
    }

    @Transactional
    @Override
    public void deleteMaster1(Master master) {
        masterRepository.delete(master);
    }

    @Override
    public Master findById(Integer masterId) {
        return masterRepository.findOne(masterId);
    }

    @Override
    public List<Master> findByHospital(Integer hospitalId) {
        List<Master> masterList = masterRepository.findMaster(hospitalId);
        return masterList;
    }

    @Override
    public List<Master> findAll() {
        return masterRepository.findAll();
    }

    @Override
    public Master findByOpenId(String openId) {
        return masterRepository.findByOpenId(openId);
    }

    @Transactional
    @Override

    public void addMasterList(List<Master> masterList) {
        Iterable<Master> masterIterable = masterList;
        masterRepository.save(masterIterable);
    }

    @Override
    public String findMasterUserName(Integer masterId) {
        Master master = masterRepository.findOne(masterId);
        return master.getUserName();

    }

    @Override
    public JSONArray findMasterByHospitalDepartmentId(Integer hospitalDepartmentId) {
        List<Master> masterList = masterRepository.findByHospitalDepartmentId(hospitalDepartmentId);
        JsonConfig config = new JsonConfig();
        config.setExcludes(new String[]{"hospitalPermissionSet", "master", "hospitalImgSet", "hospitalFieldList", "userSet", "masterList", "answer", "praiseUserSet", "answerList", "question", "masterScoreAnswer", "answerImgSet", "hospital", "masterAnswerList"});
        return JSONArray.fromObject(masterList, config);
    }

    @Override
    public String importMaster(String fileName, MultipartFile mfile) throws FileNotFoundException {
        File path = new File(ResourceUtils.getURL("classpath:").getPath());
        File uploadDir = new File(path.getAbsolutePath(), "static/images/upload/");
        if (!uploadDir.exists())
            uploadDir.mkdirs();
        if (!uploadDir.exists()) uploadDir.mkdirs();
        //新建一个文件
        File tempFile = new File(uploadDir.getAbsolutePath() + new Date().getTime() + ".xlsx");
        //初始化输入流
        InputStream is = null;
        try {
            //将上传的文件写入新建的文件中
            mfile.transferTo(tempFile);
            //根据新建的文件实例化输入流
            is = new FileInputStream(tempFile);
            //根据版本选择创建Workbook的方式
            Workbook wb = null;
            //根据文件名判断文件是2003版本还是2007版本
            if (ExcelImportUtils.isExcel2007(fileName)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = new HSSFWorkbook(is);
            }
            //根据excel里面的内容读取知识库信息
            //错误信息接收器
            String errorMsg = "";
            //得到第一个shell
            Sheet sheet = wb.getSheetAt(0);

            //得到Excel的行数
            int totalRows = sheet.getPhysicalNumberOfRows();
            //总列数
            int totalCells = 0;
            //得到Excel的列数(前提是有行数)，从第二行算起
            if (totalRows >= 2 && sheet.getRow(1) != null) {
                totalCells = sheet.getRow(1).getPhysicalNumberOfCells();
            }
            List<Master> masterList = new ArrayList<Master>();
            List<String> userNameList = new ArrayList<>();
            String br = "<br/>";

            //循环Excel行数,从第二行开始。标题不入库
            for (int r = 1; r < totalRows; r++) {
                Master master = new Master();
                master.setIsBlack("否");
                String rowMessage = "";
                Row row = sheet.getRow(r);
                if (row == null) {
                    errorMsg += br + "第" + (r + 1) + "行数据有问题，请仔细检查！";
                    continue;
                }

                //循环Excel的列
                for (int c = 0; c < totalCells; c++) {
                    Cell cell = row.getCell(c);
                    cell.setCellType(CellType.STRING);
                    if (null != cell) {
                        if (c == 0) {
                            String hospitalName = cell.getStringCellValue();
                            System.out.println(hospitalName);
                            Hospital hospital = hospitalRepository.findByName(hospitalName);
                            if (StringUtils.isEmpty(hospitalName)) {
                                rowMessage += "机构名称不能为空";
                            } else if (hospital == null) {
                                rowMessage += "机构名称不正确";
                            }
                            master.setHospital(hospital);


                        }
                        if (c == 1) {
                            String name = cell.getStringCellValue();
                            if (StringUtils.isEmpty(name)) {
                                rowMessage += "专家姓名不能为空；";
                            } else if (name.length() > 150) {
                                rowMessage += "专家姓名长度不能超过150；";
                            }
                            master.setName(name);
                        }
                        if (c == 2) {
                            String masterInfo = cell.getStringCellValue();
                            if (masterInfo.length() > 150) {
                                rowMessage += "专家信息长度不能超过150；";
                            }
                            master.setInfo(masterInfo);
                        }
                        if (c == 3) {
                            String masterGoodAt = cell.getStringCellValue();
                            if (masterGoodAt.length() > 150) {
                                rowMessage += "专家擅长领域长度不能超过150；";
                            }
                            master.setGoodAt(masterGoodAt);
                        }
                        if (c == 4) {
                            String userName = cell.getStringCellValue();

                            if (sysUserRepository.findByUserName(userName) != null) {
                                rowMessage += "用户名已存在";
                            } else if (userNameList.contains(userName)) {
                                rowMessage += "表中的用户名不可重复";

                            } else if (userName.length() > 150) {
                                rowMessage += "用户名长度不能超过150；";
                            } else if ("".equals(userName) || userName == null) {
                                rowMessage += "用户名不能为空；";
                            }
                            userNameList.add(userName);
                            master.setUserName(userName);
                        }
                        if (c == 5) {
                            String password = cell.getStringCellValue();
                            if (password.length() > 150) {
                                rowMessage += "密码长度不能超过150；";
                            } else if ("".equals(password) || password == null) {
                                rowMessage += "密码不能为空；";
                            }
                            master.setPassword(password);
                        }
                    } else {
                        rowMessage += "第" + (c + 1) + "列数据有问题，请仔细检查；";
                    }
                }

                //拼接每行的错误提示
                if (!StringUtils.isEmpty(rowMessage)) {
                    errorMsg += br + "第" + (r + 1) + "行，" + rowMessage;
                    System.out.println(errorMsg + "99999");
                } else {
                    master.setHeadImg("https://qny.liuxuanlong.xyz/timg%20%283%29.jpg");

                    masterList.add(master);
                }
            }

            //删除上传的临时文件
            if (tempFile.exists()) {
                tempFile.delete();
            }
            //导入头像
            System.out.println("*********" + masterList.size());
            List<PictureData> pictureDatas = (List<PictureData>) wb.getAllPictures();
            for (int i = 0; i < pictureDatas.size(); i++) {

                String imgName = UUID.randomUUID().toString();
                qiNiuYunUtil.uploadFiles(pictureDatas.get(i).getData(), imgName);
                masterList.get(i).setHeadImg("https://qny.liuxuanlong.xyz/" + imgName);
            }
            //全部验证通过才导入到数据库
            if (StringUtils.isEmpty(errorMsg)) {
                Iterable<Master> masterIterable = masterList;
                for (Master master1 : masterList) {
                    SysUser sysUser = new SysUser();
                    sysUser.setHospitalId(master1.getHospital().getId());
                    sysUser.setUserName(master1.getUserName());
                    sysUser.setPassword(master1.getPassword());
                    sysUser.setSysUserRoleId(3);
                    sysUserRepository.save(sysUser);
                }
                masterRepository.save(masterIterable);
                errorMsg = "导入成功，共" + masterList.size() + "条数据！";
            }

            return errorMsg;


        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return "导入出错！请检查数据格式！";
    }


}
